<?php
    class ClassDormitoryManagementDAL{

        //清空指定年级、专业、班级的宿舍分配信息
        public function removeClassDormitoryAllocate($instructorID,$removeList){
            $result=null;
            $query="update tb_student_dormitory_roster,tb_student_info_list,tb_instructor_info 
            set tb_student_dormitory_roster.status=0 
            where tb_student_dormitory_roster.student_id=:student_id;";

            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();
                
                $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                foreach($removeList as $remove){
                    $statement->bindValue(':student_id',$remove['student_id']);
                    $result=$statement->execute();
                }

                $pdo->commit();
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }
            $statement->closeCursor();

            return $result;
        }

        //获取指定年级、专业、班级列表
        public function getStudentList($grade,$major,$class){
            $query="select student_id,name 
            from tb_student_info_list 
            where grade=:grade 
            and major_id=:major_id 
            and class=:class;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            $statement->bindValue(':class',$class);

            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();

            return $result;
        }

        //判断辅导员是否符合所操作的年级、专业
        public function isInstructor($instructorID,$grade,$major){
            $query="select count(1) as count 
            from tb_instructor_info 
            where tb_instructor_info.uid=:uid 
            and tb_instructor_info.grade=:grade 
            and tb_instructor_info.major_id=:major_id;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':uid',$instructorID);
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);

            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();

            return $result;
        }

        //获取辅导员所管理的年级、专业、班级信息
        public function getInstructorGradeMajorClassInfoList($action,$instructorID,$grade,$major){
            $result=null;
            $infoList=null;

            $query_all="select SQL_CALC_FOUND_ROWS tb_instructor_info.grade,tb_major_list.major_id,tb_major_list.major_name,class 
            from tb_instructor_info,tb_major_list,tb_student_info_list 
            where tb_instructor_info.status=1 
            and tb_student_info_list.status=1 
            and tb_instructor_info.uid=:uid 
            and tb_instructor_info.grade=tb_student_info_list.grade 
            and tb_instructor_info.major_id=tb_student_info_list.major_id 
            and tb_instructor_info.major_id=tb_major_list.major_id 
            group by tb_instructor_info.grade,tb_instructor_info.major_id,class;";

            $query_grade="select SQL_CALC_FOUND_ROWS tb_instructor_info.grade,tb_major_list.major_id,tb_major_list.major_name,class 
            from tb_instructor_info,tb_major_list,tb_student_info_list 
            where tb_instructor_info.status=1 
            and tb_student_info_list.status=1 
            and tb_instructor_info.uid=:uid 
            and tb_instructor_info.grade=:grade 
            and tb_instructor_info.grade=tb_student_info_list.grade 
            and tb_instructor_info.major_id=tb_student_info_list.major_id 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            group by tb_student_info_list.grade,tb_major_list.major_id,`class`;";

            $query_major="select SQL_CALC_FOUND_ROWS tb_instructor_info.grade,tb_major_list.major_id,tb_major_list.major_name,class 
            from tb_instructor_info,tb_major_list,tb_student_info_list 
            where tb_instructor_info.status=1 
            and tb_student_info_list.status=1 
            and tb_instructor_info.uid=:uid 
            and tb_instructor_info.major_id=:major_id 
            and tb_instructor_info.grade=tb_student_info_list.grade 
            and tb_instructor_info.major_id=tb_student_info_list.major_id 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            group by tb_student_info_list.grade,tb_major_list.major_id,`class`;";

            $query_grade_major="select SQL_CALC_FOUND_ROWS tb_instructor_info.grade,tb_major_list.major_id,tb_major_list.major_name,class 
            from tb_instructor_info,tb_major_list,tb_student_info_list 
            where tb_instructor_info.status=1 
            and tb_student_info_list.status=1 
            and tb_instructor_info.uid=:uid 
            and tb_instructor_info.grade=:grade 
            and tb_instructor_info.major_id=:major_id 
            and tb_instructor_info.grade=tb_student_info_list.grade 
            and tb_instructor_info.major_id=tb_student_info_list.major_id 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            group by tb_instructor_info.grade,tb_major_list.major_id,`class`;";

            $query_page="select FOUND_ROWS() as count;";

            switch($action){
                case 0:
                    $pdo=myPDO::getInstance();
                    $statement=$pdo->prepare($query_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(':uid',$instructorID);
                    if($statement->execute()){
                        $infoList=$statement->fetchALL(PDO::FETCH_ASSOC);
                    }
                    $statement->closeCursor();
                    break;
                case 1:    
                    $pdo=myPDO::getInstance();
                    $statement=$pdo->prepare($query_grade,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(':uid',$instructorID);
                    $statement->bindValue(':grade',$grade);
                    if($statement->execute()){
                        $infoList=$statement->fetchALL(PDO::FETCH_ASSOC);
                    }
                    $statement->closeCursor();
                    break;
                case 2:
                    $pdo=myPDO::getInstance();
                    $statement=$pdo->prepare($query_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(':uid',$instructorID);
                    $statement->bindValue(':major_id',$major);
                    if($statement->execute()){
                        $infoList=$statement->fetchALL(PDO::FETCH_ASSOC);
                    }
                    $statement->closeCursor();
                    break;
                case 3:
                    $pdo=myPDO::getInstance();
                    $statement=$pdo->prepare($query_grade_major,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                    $statement->bindValue(':uid',$instructorID);
                    $statement->bindValue(':grade',$grade);
                    $statement->bindValue(':major_id',$major);
                    if($statement->execute()){
                        $infoList=$statement->fetchALL(PDO::FETCH_ASSOC);
                    }
                    $statement->closeCursor();
                    break;
                default:break;
            }

            if(!$infoList==null){
                $row=$pdo->query($query_page);
                $count=$row->fetch(PDO::FETCH_ASSOC);

                $result=array(
                    'infoList'=>$infoList,
                    'count'=>$count['count']
                );
                $row->closeCursor();
            }
            
            return $result;
        }

        //获取指定班级的学生人数
        public function getClassStudentSum($grade,$major,$class){
            $query="select count(1) as count 
            from tb_student_info_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.grade=:grade 
            and tb_student_info_list.major_id=:major_id 
            and tb_student_info_list.class=:class;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            $statement->bindValue(':class',$class);

            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();

            return $result;
        }

        //获取指定班级已分配床位的学生数量
        public function getClassAllocatedDormStudentNumber($grade,$major,$class){
            $query="select count(1) as count 
            from tb_student_info_list,tb_student_dormitory_roster 
            where tb_student_info_list.status=1 
            and tb_student_dormitory_roster.status=1 
            and tb_student_info_list.student_id=tb_student_dormitory_roster.student_id 
            and tb_student_info_list.grade=:grade 
            and tb_student_info_list.major_id=:major_id 
            and tb_student_info_list.class=:class;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            $statement->bindValue(':class',$class);

            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();

            return $result;
        }
    }
?>